#coding=gbk
import os
import time
from sql_template import *


srcfile = open('src.txt')
serial_balance = []
today = time.strftime('%Y%m%d',time.localtime())

def get_serial_balance(serial_balance):
	lines = srcfile.readline()
	cnt = 0
	while lines:
		cnt += 1
		line = lines.split()
		#sqlline = line[1]+' '+line[3] + '\n'
		#print(sqlline)
		serial_balance.append(line[1])
		line3 = '%.2f' % float(line[3])
		serial_balance.append(line3.replace('.',''))
		lines = srcfile.readline()
	return  cnt

def get_serial_str(serial_balance):
	serial_str = ''
	for i in range(len(serial_balance)):
		if i%2 != 0:
			continue
		if i == len(serial_balance)-2:
			serial_str = serial_str+repr(serial_balance[i])
		else:
			serial_str = serial_str+repr(serial_balance[i])+','
		i+=1
	return serial_str

def get_update_str(serial_balance):
	update_str = ''
	for i in range(len(serial_balance)):
		if i%2 !=0:
			continue
		update_str = update_str+template_update%(serial_balance[i+1],serial_balance[i+1],repr(serial_balance[i]))
	return update_str



sqlfile = open('adjust_clbill.sql','w+')

#print(get_serial_balance(serial_balance))
cnt = get_serial_balance(serial_balance)
serial_str = get_serial_str(serial_balance)
sqlfile.write(template_insert % serial_str )
sqlfile.write(template_bak % serial_str )
#sqlfile.write('this is  update sql\n\n\n')
update_str = get_update_str(serial_balance)
sqlfile.write(update_str)
sqlfile.write(template_some % (serial_str, serial_str,serial_str,serial_str))
sqlfile.write(template_itsm % str(cnt))


srcfile.close()
sqlfile.close()
